package org.zsqx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.zsqx.action.ZdzReportAction;
import org.zsqx.bean.CountTimes;
import org.zsqx.bean.Disaster;
import org.zsqx.bean.Dstype;
import org.zsqx.bean.Linkman;
import org.zsqx.bean.Town;
import org.zsqx.bean.WarnSet;
import org.zsqx.bean.Zdz;
import org.zsqx.bean.ZdzHoursDataRealt;
import org.zsqx.tools.OrderByBean;
import org.zsqx.tools.PropertiesTools;

import cDao.CdaoFactory;
import cDao.RealDao;

public class ZdzReportDAOImpl implements ZdzReportDao{
	
	private static List<ZdzHoursDataRealt> zdzList;
	private static String datetime;
	private static String zdzType="temp";
	private static List<Zdz> zdzInfoList;
	
	//获取自动站最新一条数据的时间
	public Date zdLastDate(){
		String sql = "select top 1 zdzdatetime from zsqxdb.dbo.zdz_hours_data_realTime order by zdzdatetime desc";
		RealDao rd = CdaoFactory.getRealDao();
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		Date zdLastDate = (Date) rd.getObjFromRs(rs);
		return zdLastDate;
	}
	public List tempReport(String datetime,OrderByBean orderByBean){
		String sql = "select id, ztemp,daymaxtemp,daymaxtemptime,daymintemp,daymintemptime," +
				" (select Z_cname from zdz where Z_id = id) as zdz_Name from zsqxdb.dbo."+getTableName(datetime);
		sql += " where zdzdatetime = '"+datetime+"' and id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		this.datetime = datetime;
		this.zdzType="temp";
		return reportList(sql);
	}
	public List rainReport(String datetime,OrderByBean orderByBean){
		String sql = "select id, minrf1/10 as minrf1,minrf2/10 as minrf2,minrf3/10 as minrf3 ,minrf4/10 as minrf4,minrf5/10 as minrf5,minrf6/10 as minrf6,minrf7/10 as minrf7,minrf8/10 as minrf8,minrf9/10 as minrf9,minrf10/10 as minrf10,minrf11/10 as minrf11,minrf12/10 as minrf12,hourrf,dayrf," +
				" ((select sum(hourrf) from zsqxdb.dbo."+getTableName(datetime)+" where zdzdatetime in(DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-0, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-1, 0)) and id=b.id)+case when datepart(minute,'"+datetime+"')=0 then 0 else hourrf end) as threehourrf," +
		" (select Z_cname from zdz where Z_id = id) as zdz_Name from zsqxdb.dbo."+getTableName(datetime)+" as b";
		sql += " where zdzdatetime = '"+datetime+"' and id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		this.datetime = datetime;
		this.zdzType="rain";
		
		return reportList(sql);
	}
	public List humidityReport(String datetime,OrderByBean orderByBean){
		String sql = "select id, rh,minrh,minrhtime,maxrh,wd10maxtime," +
		" (select Z_cname from zdz where Z_id = id) as zdz_Name from zsqxdb.dbo."+getTableName(datetime);
		sql += " where zdzdatetime = '"+datetime+"' and id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		this.datetime = datetime;
		this.zdzType="humidity";
		return reportList(sql);
	}
	public List pressureReport(String datetime,OrderByBean orderByBean){
		String sql = "select id, ps,maxps,maxpstime,minps,minpstime," +
		" (select Z_cname from zdz where Z_id = id) as zdz_Name from zsqxdb.dbo."+getTableName(datetime);
		sql += " where zdzdatetime = '"+datetime+"' and id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		this.datetime = datetime;
		this.zdzType="pressure";
		return reportList(sql);
	}
	public List windReport(String datetime,OrderByBean orderByBean){
		String sql = "select id, wd10dd,wd10df,wd10maxdd,wd10maxdf,wd10maxtime,wd3smaxdd,wd3smaxdf,wd3smaxtime,wf10daymax,wd10daymax,wd10daymaxtime,wd3daymax,wf3daymax,wd3daymaxtime," +
		" (select Z_cname from zdz where Z_id = id) as zdz_Name from zsqxdb.dbo."+getTableName(datetime);
		sql += " where zdzdatetime = '"+datetime+"' and id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		this.datetime = datetime;
		this.zdzType="wind";
		return reportList(sql);
	}
	public List severalReport(String startTime,String endTime,String zdzType,OrderByBean orderByBean){
//		String sql = "select sum(hourrf) as dayrf,max(hourrf) as hourrf,max(wd3smaxdf) as wd3smaxdf,max(wd10maxdf) as wd10maxdf,id,(select Z_cname from zdz where Z_id = id) as zdz_Name," +
//				"(select top 1 zdzdatetime from zsqxdb.dbo.zdz_hours_data_realTime where t.id=id and hourrf=max(t.hourrf) and datepart(minute,zdzdatetime)=0) as zdzdatetime," +
//				"(select top 1 wd10maxtime from zsqxdb.dbo.zdz_hours_data_realTime where t.id=id and wd10maxdf=max(t.wd10maxdf)) as wd10maxtime," +
//				"(select top 1 wd3smaxtime from zsqxdb.dbo.zdz_hours_data_realTime where t.id=id and wd3smaxdf=max(t.wd3smaxdf)) as wd3smaxtime" +
//				" from zsqxdb.dbo."+getTableName(startTime)+" as t"+
//				" where datepart(minute,zdzdatetime)=0" +
//				" and zdzdatetime between '" +startTime+"' and '"+endTime+"'"+
//				" group by id having id in("+getZdzNum(zdzType)+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		CdaoFactory.getRealDao().executeUpdate("delete from dbo.zdz_hours_data_temp", new ArrayList());
		CdaoFactory.getRealDao().executeUpdate(" insert into dbo.zdz_hours_data_temp " +
				"	select * from zsqxdb.dbo.zdz_hours_data " +
				"	where zdzdatetime between DATEADD(hh, DATEDIFF(hh,0,'"+startTime+"')+1, 0) and '"+endTime+"'", new ArrayList());
		String sql="" +
				" select " +
				" id," +
				" (select Z_cname from zdz where Z_id = id) as zdz_Name," +
				" sum(hourrf) as dayrf," +
				" max(hourrf) as hourrf," +
				" (select top 1 zdzdatetime from dbo.zdz_hours_data_temp" +
				"		where t.id=id " +
				"		and hourrf=max(t.hourrf) " +
				"		and datepart(minute,zdzdatetime)=0" +
				"		order by hourrf desc,zdzdatetime asc" +
				" )as zdzdatetime," +
				" max(wd10maxdf) as wd10maxdf," +
				" (select top 1 wd10maxtime from dbo.zdz_hours_data_temp" +
				"		where t.id=id and wd10maxdf=max(t.wd10maxdf)" +
				"		order by wd10maxdf desc,zdzdatetime asc" +
				" )as wd10maxtime," +
				" max(wd3smaxdf) as wd3smaxdf," +
				" (select top 1 wd3smaxtime from dbo.zdz_hours_data_temp" +
				"		where t.id=id and wd3smaxdf=max(t.wd3smaxdf)" +
				"		order by wd3smaxdf desc,zdzdatetime asc" +
				" )as wd3smaxtime" +
				" from dbo.zdz_hours_data_temp as t" +
				" where datepart(minute,zdzdatetime)=0" +
				" group by id" +
				" having id in("+getZdzNum(zdzType)+")"+
				" order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		//System.out.println("区间风雨报表SQL:"+sql);
		System.out.println("区间风雨:"+sql);
		
		return reportList(sql);
	}
	//过去f小时雨量
	public List pastHourReport(Integer leastHour,String propertyName,OrderByBean orderByBean){
		String sql = "select sum(hourRf) as "+propertyName+",max(wd3smaxdf) as wd3smaxdf,max(wd10maxdf) as wd10maxdf,id,(select Z_cname from zdz where Z_id = id) as zdz_Name," +
				"(select top 1 wd10maxtime from zsqxdb.dbo.zdz_hours_data_realTime where t.id=id and wd10maxdf=max(t.wd10maxdf)) as wd10maxtime," +
				"(select top 1 wd3smaxtime from zsqxdb.dbo.zdz_hours_data_realTime where t.id=id and wd3smaxdf=max(t.wd3smaxdf)) as wd3smaxtime" +
				" from zsqxdb.dbo."+getTableName(datetime)+" as t";
		sql += " where DateDiff(hour,zdzdatetime,getDate()) <= " + leastHour +
		" and datepart(minute,zdzdatetime)=0" +
		" group by id having id in("+getZdzNum()+") order by "+orderByBean.getOrderByFile()+" "+orderByBean.getOrderByMethod();
		return reportList(sql);
	}
	//过去三小时雨量
	public List pastThreeHourReport(Integer leastHour,OrderByBean orderByBean){
		return pastHourReport(leastHour, "threehourrf",orderByBean);
	}
	//过去24小时雨量
	public List pastTwentyFourHourReport(Integer leastHour,OrderByBean orderByBean){
		return pastHourReport(leastHour, "twentyfourhourrf",orderByBean);
	}
	//自动站数据更新表最早时间
	public Date getThreeMounthTableEarliestDate(){
		Date miniDate = null;
		String sql = "select min(zdzdatetime) miniDate from zsqxdb.dbo.zdz_hours_data_realTime";
		RealDao rd = CdaoFactory.getRealDao();
		ResultSet rs =rd.findBySql(sql, new ArrayList());
		try {
			if(rs.next()){
				miniDate = (Date)rs.getTimestamp("miniDate");
			}
		} catch (SQLException e) {
			rd.close();
			e.printStackTrace();
		}
		return miniDate;
	}
	//气象预警
	public List reportWarn(WarnSet warnSet,String datetime){
		String sql = "select id, hourrf," +
				"(select 'Y' where hourrf>"+warnSet.getHourrf()+" or (select sum(hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where DateDiff(hour,zdzdatetime,'"+datetime+"') < 3 and DateDiff(hour,zdzdatetime,'"+datetime+"') >=0 and datepart(minute,zdzdatetime)=0 and hd.id = outhd.id)>"+warnSet.getThreehourrf()+" or (select sum(hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where DateDiff(hour,zdzdatetime,'"+datetime+"') < 24 and DateDiff(hour,zdzdatetime,'"+datetime+"') >=0 and datepart(minute,zdzdatetime)=0 and hd.id = outhd.id)>"+warnSet.getTwentyfourhourrf()+") as rain,"+
				"(select 'Y' where wd10df>"+warnSet.getWd10df()+" or wd10maxdf>"+warnSet.getWd10maxdf()+" or wd3smaxdf>"+warnSet.getWd3smaxdf()+" or wf10daymax>"+warnSet.getWf10daymax()+" or wf3daymax>"+warnSet.getWf3daymax()+") as wind,"+
				"((select sum(hd.hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where zdzdatetime in(DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-0, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-1, 0)) and hd.id = outhd.id)+case when datepart(minute,'"+datetime+"')=0 then 0 else outhd.hourRf end) as threehourrf,"+
				"((select sum(hd.hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where zdzdatetime in(DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-0, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-1, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-2, 0),DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-3, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-4, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-5, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-6, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-7, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-8, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-9, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-10, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-11, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-12, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-13, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-14, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-15, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-16, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-17, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-18, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-19, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-20, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-21, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-22, 0), DATEADD(hh, DATEDIFF(hh,0,'"+datetime+"')-23, 0)) and hd.id = outhd.id)+case when datepart(minute,'"+datetime+"')=0 then 0 else outhd.hourRf end) as twentyfourhourrf,"+
				"(select Z_cname from zdz where Z_id = id) as zdz_Name," +
				"(select Z_type from zdz where Z_id = id) as zdz_Type," +
				"wd10dd,wd10df,wd10maxdd,wd10maxdf,wd10maxtime,wd3smaxdd,wd3smaxdf,wd3smaxtime,wd10daymax,wf10daymax,wd10daymaxtime,wd3daymax,wf3daymax,wd3daymaxtime" +
				" from zsqxdb.dbo."+getTableName(datetime)+" as outhd";
		sql += " where zdzdatetime = '"+datetime+"'";
		boolean rainwind = (warnSet.getRain()==null&&warnSet.getWind()==null);
		if(!rainwind)sql+= " and(";
		if(warnSet.getRain()!=null){
			sql+= " hourrf>"+warnSet.getHourrf();
			sql+= " or (select sum(hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where DateDiff(hour,zdzdatetime,'"+datetime+"') < 3 and DateDiff(hour,zdzdatetime,'"+datetime+"') >=0 and datepart(minute,zdzdatetime)=0 and hd.id = outhd.id)>"+warnSet.getThreehourrf();
			sql+= " or (select sum(hourRf) from zsqxdb.dbo."+getTableName(datetime)+" as hd where DateDiff(hour,zdzdatetime,'"+datetime+"') < 24 and DateDiff(hour,zdzdatetime,'"+datetime+"') >=0 and datepart(minute,zdzdatetime)=0 and hd.id = outhd.id)>"+warnSet.getTwentyfourhourrf();
			if(warnSet.getWind()!=null){
				sql += " or ";
			}
		}
		if(warnSet.getWind()!=null){
			sql+= " wd10df>"+warnSet.getWd10df();
			sql+= " or wd10maxdf>"+warnSet.getWd10maxdf();
			sql+= " or wd3smaxdf>"+warnSet.getWd3smaxdf();
			sql+= " or wf10daymax>"+warnSet.getWf10daymax();
			sql+= " or wf3daymax>"+warnSet.getWf3daymax();
		}
		if(!rainwind)sql+= ")";
			sql += " and id in("+getZdzNum()+")";
		if(warnSet.getOrder()!=null){
			sql += " order by "+warnSet.getOrderbyField()+" "+warnSet.getOrder();
		}
		return reportList(sql);
	}
	public List ReportEachHour(String date,String zdzId){
		String sql = "select id,zdzdatetime,ztemp,hourrf,rh,ps,wd10df,wd10dd,datePart(hour,zdzdatetime) as timename" +
			" from zsqxdb.dbo."+getTableName(date);
		sql += " where convert(varchar(10),zdzdatetime,120)='"+date+"'" +
				" and id = '" +zdzId+"'" +
				" and datepart(minute,zdzdatetime)=0";
		sql+="order by zdzdatetime asc";
		return reportList(sql);
	}
	public String getTableName(String date){
		Date miniDate = getThreeMounthTableEarliestDate();
		DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");   
		Date searchDate = new Date();
		try {
			searchDate = format.parse(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		if(searchDate.before(miniDate)){
			return ZdzReportAction.TOTAL_TABLE;
		}else{
			return ZdzReportAction.THREE_MONTH_TABLE;
		}
	}
	
	
	private List reportList(String sql){
		List list = null;
		try {
			list = ReportsDAOImpl.getQxjDatabaseReport(new ZdzHoursDataRealt(), sql);
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		zdzList=list;
		return list;
	}
	
	public List<ZdzHoursDataRealt> getZdzResults(){
		return zdzList;
	}
	
	private String getZdzNum(){
		RealDao rd = CdaoFactory.getRealDao();
		List<Zdz> zdzList = rd.find(new Zdz());
		StringBuffer sb = new StringBuffer();
		if(zdzList.size()>0){
			for (int i = 0; i < zdzList.size()-1; i++) {
				sb.append("'"+zdzList.get(i).getZId()+"',");
			}
			sb.append("'"+zdzList.get(zdzList.size()-1).getZId()+"'");
		}
		return sb.toString();
	}
	private String getZdzNum(String zdzType){
		RealDao rd = CdaoFactory.getRealDao();
		String sql = "";
		if(zdzType!=null){
			sql = "select Z_Id from zdz where Z_Type in ("+zdzType+")";
		}else{
			sql = "select Z_Id from zdz";
		}
		
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		List<Zdz> zdzList = rd.getListFromRs(rs, Zdz.class);
		StringBuffer sb = new StringBuffer();
		if(zdzList!=null&&zdzList.size()>0){
			for (int i = 0; i < zdzList.size()-1; i++) {
				sb.append("'"+zdzList.get(i).getZId()+"',");
			}
			sb.append("'"+zdzList.get(zdzList.size()-1).getZId()+"'");
		}
		return sb.toString();
	}
	public String getDatetime(){
		return this.datetime.substring(0,10);
	}
	@Override
	public String getZdzType() {
		// TODO Auto-generated method stub
		return this.zdzType;
	}
	@Override
	/*灾情预警*/
	public List getWarnings() {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Zdz> getZdzInfo(){
		RealDao rd=CdaoFactory.getRealDao();
		List<Zdz> zdzInfoList=rd.find(new Zdz());
		return zdzInfoList;
	}
	
	public List<Disaster> getZdzDis(String type,String town,String startTime,String endTime){
		List<Disaster> DisasterList=null;
		try {
			System.out.println(type+town+startTime+endTime);
			RealDao rd=CdaoFactory.getRealDao();
			if("".equals(type)||"全部".equals(type))type=null;
			if("".equals(town)||"全部".equals(town))town=null;
			if(startTime.equals(""))startTime=null;
			System.out.println("startTime:"+startTime);
			if("".equals(endTime))endTime=null;
			Disaster d = new Disaster();
			d.setTown(town);
			d.setDistypename(type);
			rd.setStartTime(startTime).setEndTime(endTime).setFieldTime("starttime");
			DisasterList = rd.find(d);
			System.out.println(DisasterList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return DisasterList;
	}
	
	public List<Town> getAllTowns(){
		RealDao rd=CdaoFactory.getRealDao();
		List<Town> townList=rd.find(new Town());
		return townList;
	}
	
	public List<Dstype> getAllDisTypes(){
		RealDao rd=CdaoFactory.getRealDao();
		List<Dstype> dstypeList=rd.find(new Dstype());
		return dstypeList;
	}
	public List<Town> getDisTown(String type,String town,String startTime,String endTime){
		System.out.println("type="+type+",town="+town+",startTime="+startTime+",endTime="+endTime);
		RealDao rd=CdaoFactory.getRealDao();
		String sql="select distinct town from disaster where 1=1";
		if(null==startTime||"".equals(startTime))sql+=" and starttime>='"+new SimpleDateFormat("yyyy-MM").format(new Date())+"-01 00:00:00.000"+"'";//默认当前月份
		if(null!=type&&!"".equals(type)&&!"全部".equals(type))sql+=" and distypename='"+type+"'";
		if(null!=town&&!"".equals(town)&&!"全部".equals(town))sql+=" and town='"+town+"'";
		if(null!=startTime&&!"".equals(startTime))sql+=" and starttime>='"+startTime+"'";
		if(null!=endTime&&!"".equals(endTime))sql+=" and starttime<='"+endTime+"'";
		System.out.println(sql);
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		List<Town> disTownList=new ArrayList<Town>();
		try {
			while(rs.next()){
				Town t=new Town();
				t.setTName(rs.getString("town"));
				disTownList.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			rd.close();
		}
		return disTownList;
	}
	public String getZdzBgUrl(){
		return PropertiesTools.domain+"/z/zdzReportAction!loadImage.action?time="+new Date().getTime();
	}
	//-获取自动站渲染背景图片-
	public String getZdzBg(){
		String bg = null;
		if(!zdzType.equals("temp")&&!zdzType.equals("rain"))return null;
		String table = "Surface_Temperature";
		if(zdzType.equals("temp"))table = "Surface_Temperature";
		if(zdzType.equals("rain"))table = "Surface_Rain";
		String sql = "select path from zsqx_msf.dbo."+table+" where substring(CONVERT(varchar(100), tdatetime, 20),0,14)+':00:00' = '"+datetime+"'";
		System.out.println("渲染图片SQL:"+sql);
		RealDao rd = CdaoFactory.getRealDao();
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		try {
			if(rs.next()){
				bg = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.print("渲染图图片："+bg);
		return bg;
	}
	//信息员积分计算
	public boolean linkmanCountSet(CountTimes countTimes,List<Linkman> list) throws SecurityException, SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException{
		String sql ="update linkman set count_point = lm.point,last_point=lm.point+change_point from " +
					"(select lm_num,(" +
					"isnull(upcount*t.right_score*"+countTimes.getUpcount()+",0)+" +
					"isnull(upwcount*t.right_score*"+countTimes.getUpwcount()+",0)+" +
					"isnull(uplcount*t.right_score*"+countTimes.getUplcount()+",0)+" +
					"isnull(b3count*t.right_score*"+countTimes.getB3count()+",0)+" +
					"isnull(b3wcount*t.right_score*"+countTimes.getB3wcount()+",0)+" +
					"isnull(b3lcount*t.right_score*"+countTimes.getB3lcount()+",0)+" +
					"isnull(b2count*t.right_score*"+countTimes.getB2count()+",0)+" +
					"isnull(b4count*t.right_score*"+countTimes.getB4count()+",0)+" +
					"isnull(b5count*t.right_score*"+countTimes.getB5count()+",0)+" +
					"isnull(b6count*t.right_score*"+countTimes.getB6count()+",0)+" +
					"isnull(b7count*t.right_score*"+countTimes.getB7count()+",0)+" +
					"isnull(logincount*t.right_score*"+countTimes.getLogincount()+",0)+" +
					"isnull((select top 1 visitCount from ejf_user where userId=username)*t.right_score*"+countTimes.getLogincount()+",0)" +
					") as point " +
					"from dbo.Yj_User "+
					"left outer join dbo.LINKMAN on username=lm_user left outer join dbo.linkman_type t on lm_type_id=t.id) as lm" +
					" where linkman.lm_num =lm.lm_num";
		RealDao rd = CdaoFactory.getRealDao();
		Integer i = rd.executeUpdate(sql, new ArrayList());
		if(i!=0){
			return true;
		}else{
			return false;
		}
	}
	//论坛登录次数
	public Integer getBbsLoginCount(String username){
		String sql = "select visitCount from ejf_user where userId='"+username+"'";
		RealDao rd = CdaoFactory.getRealDao();
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		int count = 0;
		try {
			if(rs.next()){
				count = rs.getInt("visitCount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	//分辨率图片设置
	public void uploadPicWidth(Double width){
		String sql = "update dbo.info_property set pic_width="+width;
		RealDao rd = CdaoFactory.getRealDao();
		rd.executeUpdate(sql, new ArrayList());
	}
	public double getPicWidth(){
		double width = 0;
		RealDao rd = CdaoFactory.getRealDao();
		String sql = "select pic_width from dbo.info_property";
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		try {
			if(rs.next()){
				width = rs.getInt("pic_width");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return width;
	}

	public static void main(String[] args) throws SecurityException, SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
		ZdzReportDAOImpl zd = new ZdzReportDAOImpl();
		System.out.println(zd.zdLastDate());
	}
}
